/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.displaytag.properties.SortOrderEnum;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.istt.dao.FileMgrDao;
import org.istt.domain.File;
import org.istt.domain.FileManagement;
import org.istt.domain.User;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * @author Huy
 * 
 */
@Repository(value = "fileMgrDao")
public class FileMgrDaoHibernate extends BaseDaoHibernate<FileManagement, Long> implements
		FileMgrDao {
	
	private Log logger = LogFactory.getLog(getClass());
	
	@Autowired
	public FileMgrDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#find(java.lang.Long, java.lang.String, java.util.Date)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<List<Object>> find(final Long fieldId, final String content, final Date toDate) {
		return (List<List<Object>>) hibernateTemplate.execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder builder = new StringBuilder();
				builder.append("select {fi.*}, ps.status, ps.id as file_management_id from TBL_FILE fi inner join ( ");
				builder.append("	select x.* from ( ");
				builder.append("		select  ");
				builder.append("			fm.ID,  ");
				builder.append("			fm.FILE_ID,  ");
				builder.append("			wp.POSITION,  ");
				builder.append("			MAX(POSITION) over(PARTITION BY FILE_ID) as CP,  ");
				builder.append("			ps.STATUS  ");
				builder.append("		from TBL_FILE_MANAGEMENT fm inner join TBL_WORKFLOW_PROCESS wp on fm.WORKFLOW_PROCESS_ID = wp.ID ");
				builder.append("		inner join TBL_PROCESS_STATUS ps on ps.ID = wp.PROCESS_STATUS_ID ");
				builder.append("	) x ");
				builder.append("	where POSITION = CP ");
				builder.append(") ps on fi.ID = ps.FILE_ID ");
				 
				if (fieldId != null) {
					builder.append(" inner join TBL_CATEGORY c on fi.CATEGORY_ID = c.ID inner join TBL_FIELD fd on c.FIELD_ID = fd.ID ");
				}
				builder.append(" where fi.content like :ct");
				if (toDate != null) {
					builder.append(" and fi.OUTPUT_DATE <= :toDate" );
				}
				
				if (fieldId != null) {
					builder.append(" and fd.id = :fieldId" );
				}
				
				SQLQuery query = session.createSQLQuery(builder.toString())
										.addEntity("fi", File.class)
										.addScalar("status", Hibernate.STRING)
										.addScalar("file_management_id", Hibernate.LONG); 
				query.setString("ct", "%" + content + "%");
				 
				if (toDate != null) {
					query.setDate("toDate", toDate);
				}
				
				if (fieldId != null) {
					query.setLong("fieldId", fieldId);
				}
				return query.list();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#countByFileId(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public int countByFileId(final String fileId) {
		return (Integer)hibernateTemplate.execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return ((Long)session.createCriteria(FileManagement.class)
								.createCriteria("file")
								.add(Restrictions.idEq(fileId))
								.setProjection(Projections.rowCount())
								.uniqueResult()).intValue();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.hibernate.BaseDaoHibernate#makePersistent(org.istt.domain.BaseObject)
	 */
	@Override
	public FileManagement makePersistent(FileManagement entity) {
		return (FileManagement) hibernateTemplate.merge(entity);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#approveOfLatestFile(java.lang.String[], org.istt.domain.User)
	 */
	@Override
	public void approveOfLatestFile(final String[] fmgrIds, final User approver) {
		
		int n = (Integer) hibernateTemplate.execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder builder = new StringBuilder();
				
				builder.append("update TBL_FILE_MANAGEMENT set APPROVER_ID =?, APPROVED_DATE =? where FILE_ID IN ( ");
				
				String wherec = "";
				for (int i = 0; i < fmgrIds.length; i++) {
					wherec += "?, ";
				}
				
				builder.append(wherec.substring(0, wherec.length() - 2) + " )");
				SQLQuery query = session.createSQLQuery(builder.toString()); 
				
				query.setLong(0, approver.getId());
				query.setTimestamp(1, new Date());
				
				for (int i = 0; i < fmgrIds.length; i++) {
					query.setString(i + 2, fmgrIds[i]);
				}
				
				return query.executeUpdate();
			}
		});
		
		logger.info(n + " records has been updated!");
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<FileManagement> findFileMgrs(final String fileId, final Boolean activeStatus, final Integer position) {
		return (List<FileManagement>) hibernateTemplate.execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(FileManagement.class)
				//.add(Expression.isNotNull("sender"))
				//.add(Expression.isNull("approver"))
				.setFetchMode("File", FetchMode.JOIN)
				//.setFetchMode("WorkflowProcess", FetchMode.JOIN)
				;
				criteria.createAlias("file", "t1");
				if (position != 0) {
					criteria.setFetchMode("WorkflowProcess", FetchMode.JOIN);
					criteria.createAlias("workflowProcess", "t2");
					criteria.add(Restrictions.eq("t2.position", position));
				}
				
				if (activeStatus == false) {
					criteria.add(Restrictions.eq("activeStatus", false));
				}
				if (fileId != null) {
					criteria.add(Restrictions.eq("t1.id", fileId));
				}
				
				
				return criteria.list();

			}
		});		
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#removeAmendment(java.lang.Long[])
	 */
	@Override
	public void removeAmendment(Long[] ids) {
		StringBuilder builder = new StringBuilder();
		builder.append("update fileMgr f set ");
		builder.append("f.approvedDate = ?, f.approvedComment = ?, f.activeStatus = ?, f.approveStatus = ?, f.viewStatus = ?, f.receivedDate = ?, f.receiverRejectFile = ?");
		builder.append("where f.id in (");
		
		for (int i = 0; i < ids.length - 1; i++) {
			builder.append("?, ");
		}
		
		builder.append("?)");
		
		List<Object> tmp = new ArrayList<Object>();
		
		tmp.add(null);//f.approvedDate
		tmp.add(null);//f.approvedComment
		tmp.add(false); // f.activeStatus
		tmp.add(null); //f.approveStatus
		tmp.add(false); // f.viewStatus
		tmp.add(DateTime.now()); // f.receivedDate
		tmp.add(null); // f.receiverRejectFile
		
		tmp.addAll(Arrays.asList(ids));
		
		hibernateTemplate.bulkUpdate(builder.toString(), tmp.toArray(new Object[]{}));
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#getAmendmentFile(int, int, org.displaytag.properties.SortOrderEnum, java.lang.String)
	 */
	@Override
	public List<FileManagement> getAmendmentFile(final Long userId, final int firstRecordIndex,
			final int pageSize, final SortOrderEnum sortDirection, final String sortCriterion) {
		return hibernateTemplate.execute(new HibernateCallback<List<FileManagement>>() {

			@SuppressWarnings("unchecked")
			@Override
			public List<FileManagement> doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria cr = session.createCriteria(FileManagement.class).add(Restrictions.eq("receiverRejectFile.id", userId)).addOrder(Order.desc("receivedDate"));
				if (sortCriterion != null) {
					cr.addOrder(sortDirection == SortOrderEnum.ASCENDING ? Order.asc(sortCriterion) : Order.desc(sortCriterion));
				}
				
				return cr.setFirstResult(firstRecordIndex).setMaxResults(pageSize).list();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#getAmendmentFileRecordCount()
	 */
	@Override
	public int getAmendmentFileRecordCount(final Long userId) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				return ((Long) session.createCriteria(FileManagement.class)
										.add(Restrictions.eq("receiverRejectFile.id", userId))
										.setProjection(Projections.rowCount()).uniqueResult()).intValue();
			}
			
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#getCurrentStep(java.lang.String)
	 */
	@Override
	public int getCurrentStep(final String fileId) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {
			
			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				String hsql = new StringBuilder("select wp.position from fileMgr fm join fm.workflowProcess wp where fm.file.id = :fileId")
									.append(" and (fm.approveStatus is null or fm.approveStatus = false)")
									.toString();
				Integer result = 0;
				try {
					Query query = session.createQuery(hsql);
					query.setParameter("fileId", fileId);
					
					result = (Integer) query.uniqueResult();
					if (result == null) {
						System.out.println("Not found unique result");
						query = session.createQuery("select max(wp.position) from fileMgr fm join fm.workflowProcess wp where fm.file.id = :fileId");
						query.setParameter("fileId", fileId);
						result = (Integer) query.uniqueResult();
						System.out.println("result: "+result);
					}
				
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return result;
			}
			
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileMgrDao#getCurrentStepProcess(java.lang.String)
	 */
	@Override
	public FileManagement getCurrentStepProcess(final String fileId) {
			return hibernateTemplate.execute(new HibernateCallback<FileManagement>() {
				@Override
				public FileManagement doInHibernate(Session session)
						throws HibernateException, SQLException {
					
					String hsql = new StringBuilder("from fileMgr fm where fm.file.id = :fileId")
										.append(" and (fm.approveStatus is null or fm.approveStatus = false)")
										.toString();
					
					Query query = session.createQuery(hsql);
					query.setParameter("fileId", fileId);
					return (FileManagement) query.uniqueResult();
				}
			});
	}

}
